package day02;

import util.JdbcUtil;

import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;
import java.util.Vector;

public class DataBaseQueryer {

    private JFrame jf;
    private JLabel jl;
    private JTextField jtf;
    private JButton jb;

    public DataBaseQueryer() {
        jf=new JFrame("数据库查询器");
        jl=new JLabel("查询语句：");
        jtf=new JTextField(40);
        jb=new JButton("查询");

    }

    private void init(){
        JPanel jp=new JPanel();
        jp.add(jl);
        jp.add(jtf);
        jp.add(jb);
        jf.add(jp, BorderLayout.NORTH);
    }

    private void addEventHandler(){
        jb.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                String sql=jtf.getText();

                Connection con=null;
                PreparedStatement ps=null;
                ResultSet rs=null;
                try{
                    con= JdbcUtil.getConnection();
                    con.setAutoCommit(false);

                    ps=con.prepareStatement(sql);

                    rs=ps.executeQuery();

                    DefaultTableModel dtm=new DefaultTableModel();
                    JTable jt=new JTable(dtm);
                    JScrollPane jsp=new JScrollPane(jt);

                    jf.add(jsp);

                    ResultSetMetaData rsmd=rs.getMetaData();

                    int columnCount=rsmd.getColumnCount();

                    for (int i = 0; i < columnCount; i++) {
                        String columnName=rsmd.getColumnName(i+1);
                        dtm.addColumn(columnName);
                    }

                    while(rs.next()){
                        Vector<String> rowData=new Vector<>();
                        for (int i = 0; i < columnCount; i++) {
                            rowData.add(rs.getString(i+1));
                        }
                        dtm.addRow(rowData);
                    }

                    con.commit();
                }catch (Exception ex){
                    JOptionPane.showMessageDialog(jf,ex.toString());
                    try {
                        con.rollback();
                    } catch (SQLException e1) {
                        e1.printStackTrace();
                    }
                }finally {
                    JdbcUtil.close(con,ps,rs);
                }
                jf.setVisible(true);
            }
        });

        jtf.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                String sql=jtf.getText();

                Connection con=null;
                PreparedStatement ps=null;
                ResultSet rs=null;
                try{
                    con= JdbcUtil.getConnection();
                    con.setAutoCommit(false);

                    ps=con.prepareStatement(sql);

                    rs=ps.executeQuery();

                    DefaultTableModel dtm=new DefaultTableModel();
                    JTable jt=new JTable(dtm);
                    JScrollPane jsp=new JScrollPane(jt);

                    jf.add(jsp);

                    ResultSetMetaData rsmd=rs.getMetaData();

                    int columnCount=rsmd.getColumnCount();

                    for (int i = 0; i < columnCount; i++) {
                        String columnName=rsmd.getColumnName(i+1);
                        dtm.addColumn(columnName);
                    }

                    while(rs.next()){
                        Vector<String> rowData=new Vector<>();
                        for (int i = 0; i < columnCount; i++) {
                            rowData.add(rs.getString(i+1));
                        }
                        dtm.addRow(rowData);
                    }

                    con.commit();
                }catch (Exception ex){
                    JOptionPane.showMessageDialog(jf,ex.toString());
                    try {
                        con.rollback();
                    } catch (SQLException e1) {
                        e1.printStackTrace();
                    }
                }finally {
                    JdbcUtil.close(con,ps,rs);
                }
                jf.setVisible(true);
            }
        });
    }

    public void show(){
        init();
        addEventHandler();
        jf.setSize(600,400);
        jf.setVisible(true);
        jf.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    }

    public static void main(String[] args) {
        new DataBaseQueryer().show();
    }
}
